//bug: import while resultset opened

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.AbstractAction;
import javax.swing.Action;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JComboBox.KeySelectionManager;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.RowSorter;
import javax.swing.SwingWorker;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.table.DefaultTableColumnModel;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableCellRenderer;
import javax.swing.table.TableColumn;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;

//This is a project for Bai-Sha junior high school.
//year, student_id, name, sex, person_id, birth, graduate, addr, reg, serial, comment
public class Main extends JFrame {
	
	private final Map<String, Action> actions=new HashMap<String, Action>();
	public static DBManager stuDB;
	private final JTable resultTable=new JTable();
	//public ResultSetTableModel resultSetTableModel;
	private ResultSet resultSet;
	private final TableCellRenderer lineNumberRenderer=new RowNumberRenderer();
	private ImportDialog importDialog=null;
	public final SearchPane searchPane=new SearchPane();
	
	static {
		setupLookAndFeel();
		try {
			stuDB=new DBManager("jdbc:derby:studentlist");
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "無法建立資料庫連線");
			System.exit(1);
		}
	}
	
	public static void main(String[] args) {
		final Main m=new Main();
		m.setVisible(true);
	}
	
	public Main() {
		setTitle("白沙國中畢業學生名冊");
		setSize(600, 400);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		
		setupActions();

		setupMenus();
		
		getContentPane().setLayout(new BorderLayout());
		setupSearchPane();
		setupUpdatePane();
		add(new JScrollPane(resultTable), BorderLayout.CENTER);
		
		this.addWindowListener(new WindowAdapter() {
			@Override
			public void windowClosing(WindowEvent arg0) {
				cleanup();
			}
		});
	}
	
	private void setupActions() {
		actions.put("exit", new ExitAction());
		actions.put("import", new ImportAction());
	}

	private class ExitAction extends AbstractAction {
		public ExitAction() {
			super("離開");
			putValue(AbstractAction.SHORT_DESCRIPTION, "Exit the program.");
			//putValue(AbstractAction.MNEMONIC_KEY, KeyEvent.VK_X);
		}
		@Override
		public void actionPerformed(ActionEvent e) {
			cleanup();
			System.exit(0);
		}
	} 
	private class ImportAction extends AbstractAction {
		public ImportAction() {
			super("匯入...");
			putValue(AbstractAction.SHORT_DESCRIPTION, "Import data into DB.");
			//putValue(AbstractAction.MNEMONIC_KEY, KeyEvent.VK_I);
		}
		@Override
		public void actionPerformed(ActionEvent e) {
			if(importDialog==null) importDialog=new ImportDialog(Main.this);
			importDialog.setVisible(true);
		}
	} 

	private void setupMenus() {
		final JMenuItem exitItem=new JMenuItem(actions.get("exit"));
		final JMenuItem importItem=new JMenuItem(actions.get("import"));
		final JMenu fileMenu=new JMenu("檔案");
		fileMenu.add(importItem);
		fileMenu.add(exitItem);
		
		final JMenuBar menuBar=new JMenuBar();
		menuBar.add(fileMenu);
		
		this.setJMenuBar(menuBar);
	}
	private String getColumnName(Object chineseName) {
		if(chineseName.equals("學號")) {
			return "id";
		} else if(chineseName.equals("姓名")) {
			return "name";
		} else if(chineseName.equals("生日")) {
			return "birth";
		} else if(chineseName.equals("地址")) {
			return "addr";
		} else {
			throw new RuntimeException();
		}
	}
	
	class SearchPane extends JPanel {
		private final JComboBox searchCombo;
		private final JTextField searchField;
		private final JLabel statLabel=new JLabel();
		public SearchPane() {
			searchCombo=new JComboBox(
					new String[] {"學號", "姓名", "生日", "地址"});
			searchCombo.setSelectedIndex(1);
			searchField=new JTextField(10);
			final JButton searchButton=new JButton("搜尋");
			
			//final JPanel searchPane=new JPanel();
			this.setLayout(new FlowLayout(FlowLayout.LEFT));
			this.add(searchCombo);
			this.add(searchField);
			this.add(searchButton);
			this.add(statLabel);
			
			final ActionListener search=new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					search();
				}
			};
			searchField.addActionListener(search);
			searchButton.addActionListener(search);	
		}
		public void search() {
			final String query=searchField.getText().trim();
			if(query.equals("")) return;
			final String col=getColumnName(searchCombo.getSelectedItem());
			sendQuery(query, col);
		}
		private void sendQuery(String query, String col) {
			clearResults();
			//statLabel.setText("搜尋中...");
			if(query.contains("*") || query.contains("?")) {
				query=query.replace('*', '%');
				query=query.replace('?', '_');
				resultSet=stuDB.execute("Select * from students Where "+col+" like ?", query);
			} else {
				if(query.charAt(0)=='<') {
					resultSet=stuDB.execute("Select * from students Where "+col+" < ?", query.substring(1));
				} else if(query.charAt(0)=='>') {
					resultSet=stuDB.execute("Select * from students Where "+col+" > ?", query.substring(1));
				} else {
					resultSet=stuDB.execute("Select * from students Where "+col+" = ?", query);	
				}
			}
			setupResultTable(resultSet);
			
		}
		private void setupResultTable(ResultSet rs) {
			final TableModel model=new NumberedResultSetTableModel(rs);
			statLabel.setText(model.getRowCount()+"筆資料");
			resultTable.setModel(model);
			resultTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
			//resultTable.setAutoCreateRowSorter(true);
			
			final TableRowSorter<TableModel> sorter=new TableRowSorter<TableModel>(model);
			sorter.setComparator(0, new Comparator<Object>() {
				@Override
				public int compare(Object a, Object b) {
					return Integer.valueOf(a.toString()).compareTo(
							Integer.valueOf(b.toString()));
				}
			});
			resultTable.setRowSorter(sorter);
			
			resultTable.getColumnModel().getColumn(0).setPreferredWidth(30);
			resultTable.getColumnModel().getColumn(0).setCellRenderer(lineNumberRenderer);
			
		}
		public void reset() {
			searchField.setText("");
			statLabel.setText("");
		}
	}
	private void setupSearchPane() {
		add(searchPane, BorderLayout.NORTH);
	}
	
	public void clearResults() {
		resultTable.setAutoCreateRowSorter(true);
		resultTable.setModel(new DefaultTableModel());
		closeResultSet();
	}
	
	private void closeResultSet() {
		try {
			if(resultSet!=null && !resultSet.isClosed()) resultSet.close(); //close old rs
			resultSet=null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void cleanup() {
		clearResults();
		stuDB.close();
	}
	
	private class DeleteAction extends AbstractAction {
		public DeleteAction() {
			super("刪除");
		}
		@Override
		public void actionPerformed(ActionEvent arg0) {
			final int[] indexes=resultTable.getSelectedRows();
			if(indexes.length==0) return;
			final int res=JOptionPane.showConfirmDialog(
					Main.this, "確定刪除此"+indexes.length+" 筆資料?", "刪除資料", JOptionPane.YES_NO_OPTION);
			if(res!=JOptionPane.YES_OPTION) return;
			try {
				for(int i=indexes.length-1; i>=0; i--) {
					resultSet.absolute(indexes[i]+1);
					resultSet.deleteRow();	
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
			searchPane.search();
		}
	}
	class InsertAction extends AbstractAction {
		public InsertAction() {
			super("新增");
			//this.setEnabled(false);
		}
		@Override
		public void actionPerformed(ActionEvent e) {
		 //year, student_id, name, sex, person_id, birth, graduate, addr, reg, serial, comment
			
		   /*try {
			   //>>>
			   
            resultSet.moveToInsertRow(); 
            resultSet.updateString(1, "AINSWORTH");  
            resultSet.updateInt(2,35); 
            resultSet.updateBoolean(3, true); 
            resultSet.insertRow();
            resultSet.moveToCurrentRow();
           
         } catch (SQLException e1) {
            e1.printStackTrace();
         } 
          */
		}
	}
	class UpdateAction extends AbstractAction {
		public UpdateAction() {
			super("修改");
			this.setEnabled(false);
		}
		@Override
		public void actionPerformed(ActionEvent e) {
			
		}
	}
	private void setupUpdatePane() {
		final JButton insertButton=new JButton(new InsertAction());
		final JButton updateButton=new JButton(new UpdateAction());
		final JButton deleteButton=new JButton(new DeleteAction());
		final JButton clearButton=new JButton("重置");
		clearButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				searchPane.reset();
				clearResults();
			}
		});
		final JPanel pane=new JPanel();
		pane.setLayout(new FlowLayout(FlowLayout.LEADING));
		pane.add(insertButton);
		pane.add(updateButton);
		pane.add(deleteButton);
		pane.add(clearButton);
		add(pane, BorderLayout.SOUTH);
	}
	
	private static void setupLookAndFeel() {
		try {
	        UIManager.setLookAndFeel(
	            UIManager.getSystemLookAndFeelClassName());
	    } catch (UnsupportedLookAndFeelException e) {
	    	e.printStackTrace();
	    } catch (ClassNotFoundException e) {
	    	e.printStackTrace();
	    } catch (InstantiationException e) {
	    	e.printStackTrace();
	    } catch (IllegalAccessException e) {
	       e.printStackTrace();
	    }
	}
	
}
